﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.OracleClient;

namespace KPD_Admin
{
    public partial class AddEmpForm : Form
    {
        static string con_string = "Data Source=virtual/orcl;Persist Security Info=True;User ID=KPD;Password=KPD;Unicode=True";
        DB new_db = new DB();
        private DataSet jobs_ds;
        private DataSet emp_ds;
        private OracleCommand cmd;
        private OracleDataAdapter da;
        private OracleCommandBuilder cb;
        string upd_id;
        bool ins_or_upd = false;


        public AddEmpForm()
        {
            InitializeComponent();
        }

        public AddEmpForm(string id)
        {
            InitializeComponent();
            Fill_Jobs();
            Fill_To_Edit(id);
            ins_or_upd = true;
            upd_id = id;
            InitializeComponent();
        }

        private void Fill_To_Edit(string id)
        {
            OracleConnection mycon = new OracleConnection(con_string);
            mycon.Open();

            string sql = "select emp_surname, emp_first_name, emp_last_name,emp_email, emp_phone_number, emp_salary, job_id, emp_work_rate, emp_login, emp_pass  from employees where emp_id=:ID";
            cmd = new OracleCommand(sql, mycon);
            cmd.CommandType = CommandType.Text;
            cmd.Parameters.Add(new OracleParameter(":ID", OracleType.VarChar)).Direction = ParameterDirection.Input;
            cmd.Parameters[":ID"].Value = id;
            da = new OracleDataAdapter(cmd);
            cb = new OracleCommandBuilder(da);
            emp_ds = new DataSet();
            da.Fill(emp_ds);
            mycon.Close();
            SurnameBox.DataBindings.Add("Text", emp_ds.Tables[0], "emp_surname");
            string ab = SurnameBox.Text;
            FirstNameBox.DataBindings.Add("Text", emp_ds.Tables[0], "emp_first_name");
            LastNameBox.DataBindings.Add("Text", emp_ds.Tables[0], "emp_last_name");
            EmailBox.DataBindings.Add("Text", emp_ds.Tables[0], "emp_email");
            PhoneBox.DataBindings.Add("Text", emp_ds.Tables[0], "emp_phone_number");
            SalaryBox.DataBindings.Add("Text", emp_ds.Tables[0], "emp_salary");
            WorkDayBox.DataBindings.Add("Text", emp_ds.Tables[0], "emp_work_rate");
            loginBox.DataBindings.Add("Text", emp_ds.Tables[0], "emp_login");
            passBox.DataBindings.Add("Text", emp_ds.Tables[0], "emp_pass");
            string name_curent_job = new_db.get_job_name(emp_ds.Tables[0].Rows[0]["job_id"].ToString());
            int index_curent_job = JobComboBox.FindString(name_curent_job);
            JobComboBox.SelectedIndex = index_curent_job;
            
            

        }
        private int Job_Pos(string job_name)
        {

            for (int i = 0; i <jobs_ds.Tables[0].Rows.Count; i++)
            { if (jobs_ds.Tables[0].Rows[i]["JOB_TITLE"].ToString()==job_name) return i;
            }
            return 0;
        }

        private void AddEmpForm_Load(object sender, EventArgs e)
        {
            Fill_Jobs();
        }

        private void Fill_Jobs()
        {
            OracleConnection mycon = new OracleConnection(con_string);
            mycon.Open();

            string sql = "select * from jobs";
            cmd = new OracleCommand(sql, mycon);
            cmd.CommandType = CommandType.Text;

            da = new OracleDataAdapter(cmd);
            cb = new OracleCommandBuilder(da);
            jobs_ds = new DataSet();

            da.Fill(jobs_ds);
            
            JobComboBox.DataSource = jobs_ds.Tables[0];
            JobComboBox.DisplayMember = "JOB_TITLE";
            JobComboBox.ValueMember = "JOB_TITLE";
          //  JobComboBox.SelectedValue = "";
            
            
            mycon.Close();
        }
        private Boolean CheckSalary(int sal,int min, int max)
        {
            if ((sal<min)||(sal>max))
            {
                return false;
            }else return true;
        }

        private void SaveButton_Click(object sender, EventArgs e)
        {
            string[] val = new string[3];
            select_job_id(val);
            string salary_from_box = emp_ds.Tables[0].Rows[0]["emp_salary"].ToString();
            if (ins_or_upd)
            {
                if (CheckSalary(Convert.ToInt32(salary_from_box), Convert.ToInt32(val[1]), Convert.ToInt32(val[2])))
                {
                    OracleConnection mycon = new OracleConnection(con_string);
                    OracleCommand cmd = new OracleCommand("UPDATE EMPLOYEES SET EMP_SURNAME=? WHERE EMP_ID = ?", mycon);
                    //, EMP_FIRST_NAME=@EMP_FIRST_NAME, EMP_LAST_NAME=@EMP_LAST_NAME, EMP_EMAIL=@EMP_EMAIL, EMP_PHONE_NUMBER=@EMP_PHONE_NUMBER, EMP_SALARY=@EMP_SALARY, JOB_ID=@JOB_ID, EMP_WORK_RATE=@EMP_WORK_RATE,EMP_LOGIN=@EMP_LOGIN,EMP_PASS=@EMP_PASS
                    cmd.Parameters.Add("EMP_SURNAME", OracleType.VarChar, 30, "EMP_SURNAME"); 
                    cmd.Parameters.Add("EMP_ID", OracleType.Number, 10, "EMP_ID");
                    da.UpdateCommand = cmd;
                    da.Update(emp_ds);
                   
                    //da.Update(emp_ds, emp_ds.Tables[0].ToString());   
                }
                else MessageBox.Show("Проверьте зарплата", "Ошибка");
            }
            else
            {
                if (CheckSalary(Convert.ToInt32(SalaryBox.Text.ToString()), Convert.ToInt32(val[1]), Convert.ToInt32(val[2])))
                {

                    OracleConnection mycon = new OracleConnection(con_string);
                    mycon.Open();
                    cmd = new OracleCommand("INSERT INTO EMPLOYEES (EMP_SURNAME, EMP_FIRST_NAME, EMP_LAST_NAME, EMP_HIRE_DATE, EMP_EMAIL, EMP_PHONE_NUMBER, EMP_SALARY, JOB_ID, EMP_WORK_RATE,EMP_LOGIN,EMP_PASS) VALUES ('" + SurnameBox.Text + "', '" + FirstNameBox.Text + "', '" + LastNameBox.Text + "', SYSDATE, '" + EmailBox.Text + "', '" + PhoneBox.Text + "', '" + SalaryBox.Text + "', '" + val[0] + "', '" + WorkDayBox.Text + "','" + loginBox.Text + "','" + passBox.Text + "')", mycon);
                    cmd.ExecuteNonQuery();
                    mycon.Close();
                    this.Close();
                }
                else MessageBox.Show("Проверьте зарплата", "Ошибка");
            }
        }
        private void select_job_id(string[] val)
        {
            DataRow[] darow;
            darow = jobs_ds.Tables[0].Select("JOB_TITLE='" + JobComboBox.SelectedValue + "'");
            val[0] = darow[0].ItemArray[0].ToString();//id
            val[1] = darow[0].ItemArray[2].ToString();//min salary
            val[2] = darow[0].ItemArray[3].ToString();//max salary
            
        }

        private string select_min_max(string id)
        {
            DataRow[] darow;
            darow = jobs_ds.Tables[0].Select("MAX_SALARY='" + JobComboBox.SelectedValue + "'");
            string st = darow[0].ItemArray[0].ToString();
            return st;
        }

        
    }
}
